package yiran.stock.data;

import org.bukkit.plugin.java.JavaPlugin;
import yiran.stock.output.Logger;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class DatabaseManager {
    private static Connection connection;
    private static JavaPlugin pluginInstance;
    private static DatabaseType dbType;

    public enum DatabaseType {
        SQLITE, MYSQL
    }

    public static void connect(JavaPlugin plugin) {
        pluginInstance = plugin;
        try {
            String dbTypeStr = plugin.getConfig().getString("database.type", "sqlite").toUpperCase();
            dbType = DatabaseType.valueOf(dbTypeStr);

            if (dbType == DatabaseType.SQLITE) {
                connectSQLite(plugin);
            } else if (dbType == DatabaseType.MYSQL) {
                connectMySQL(plugin);
            }

            createTables();
        } catch (Exception e) {
            Logger.error("数据库连接失败: " + e.getMessage());
        }
    }

    private static void connectSQLite(JavaPlugin plugin) throws Exception {
        File dataFolder = plugin.getDataFolder();
        if (!dataFolder.exists()) {
            if (!dataFolder.mkdirs()) {
                String errorMsg = "无法创建数据文件夹: " + dataFolder.getAbsolutePath();
                Logger.error(errorMsg);
                throw new IOException(errorMsg);
            }
        }

        String filename = plugin.getConfig().getString("database.sqlite.filename", "stock_data.db");
        File dbFile = new File(dataFolder, filename);
        String dbPath = dbFile.getAbsolutePath();

        Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection("jdbc:sqlite:" + dbPath);

        connection.setNetworkTimeout(Runnable::run, 5000);
    }

    private static void connectMySQL(JavaPlugin plugin) throws Exception {
        String host = plugin.getConfig().getString("database.mysql.host", "localhost");
        int port = plugin.getConfig().getInt("database.mysql.port", 3306);
        String database = plugin.getConfig().getString("database.mysql.database", "minecraft");
        String username = plugin.getConfig().getString("database.mysql.username", "root");
        String password = plugin.getConfig().getString("database.mysql.password", "");
        String parameters = plugin.getConfig().getString("database.mysql.parameters", "");

        String url = String.format("jdbc:mysql://%s:%d/%s%s", host, port, database, parameters);

        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection(url, username, password);

        connection.setNetworkTimeout(Runnable::run, 5000);
    }
    
    private static void ensureConnection() throws SQLException {
        if (connection == null || connection.isClosed() || !connection.isValid(5)) {
            if (pluginInstance != null) {
                connect(pluginInstance);
            }
        }
    }

    public static void disconnect() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            Logger.error("数据库关闭失败: " + e.getMessage());
        }
    }

    private static void createTables() throws SQLException {
        String autoIncrement = (dbType == DatabaseType.MYSQL) ? "AUTO_INCREMENT" : "AUTOINCREMENT";

        String sql = "CREATE TABLE IF NOT EXISTS holdings (" +
                "id INTEGER PRIMARY KEY " + autoIncrement + "," +
                "player_name TEXT NOT NULL," +
                "stock_symbol TEXT NOT NULL," +
                "quantity INTEGER NOT NULL DEFAULT 0," +
                "purchase_price REAL NOT NULL," +
                "purchase_time BIGINT NOT NULL," + // 新增购买时间字段
                "UNIQUE(player_name, stock_symbol)" +
                ");";

        try (Statement stmt = connection.createStatement()) {
            stmt.execute(sql);
        }
    }

    public static void addHolding(String playerName, String symbol, int quantity, double purchasePrice) {
        long currentTime = System.currentTimeMillis();

        try {
            ensureConnection();
            connection.setAutoCommit(false);

            String updateSql = "UPDATE holdings SET quantity = quantity + ?, " +
                    "purchase_price = CASE WHEN ? > 0 THEN (purchase_price * quantity + ? * ?) / (quantity + ?) ELSE purchase_price END, " +
                    "purchase_time = ? " + // 更新购买时间
                    "WHERE player_name = ? AND stock_symbol = ?";

            try (PreparedStatement updateStmt = connection.prepareStatement(updateSql)) {
                updateStmt.setInt(1, quantity);
                updateStmt.setInt(2, quantity);
                updateStmt.setDouble(3, purchasePrice);
                updateStmt.setInt(4, quantity);
                updateStmt.setInt(5, quantity);
                updateStmt.setLong(6, currentTime);
                updateStmt.setString(7, playerName);
                updateStmt.setString(8, symbol);

                int rowsAffected = updateStmt.executeUpdate();

                if (rowsAffected == 0) {
                    String insertSql = "INSERT INTO holdings (player_name, stock_symbol, quantity, purchase_price, purchase_time) " +
                            "VALUES (?, ?, ?, ?, ?)";

                    try (PreparedStatement insertStmt = connection.prepareStatement(insertSql)) {
                        insertStmt.setString(1, playerName);
                        insertStmt.setString(2, symbol);
                        insertStmt.setInt(3, quantity);
                        insertStmt.setDouble(4, purchasePrice);
                        insertStmt.setLong(5, currentTime);
                        insertStmt.executeUpdate();
                    }
                }
            }
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback(); // 回滚事务
            } catch (SQLException ex) {
                Logger.error("事务回滚失败: " + ex.getMessage());
            }

            Logger.error("添加持仓失败: " + e.getMessage());
            Logger.error("SQL参数: player=" + playerName + ", symbol=" + symbol +
                    ", qty=" + quantity + ", price=" + purchasePrice);
            throw new RuntimeException("数据库操作失败", e);
        } finally {
            try {
                if (connection != null) connection.setAutoCommit(true);
            } catch (SQLException e) {
                Logger.error("恢复自动提交失败: " + e.getMessage());
            }
        }
    }

    public static Map<String, Integer> getHoldings(String playerName) {
        Map<String, Integer> holdings = new HashMap<>();
        String sql = "SELECT LOWER(stock_symbol) as stock_symbol, quantity FROM holdings WHERE player_name = ?";

        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, playerName); // 使用玩家名称
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                holdings.put(rs.getString("stock_symbol"), rs.getInt("quantity"));
            }
        } catch (SQLException e) {
            Logger.error("获取持仓失败: " + e.getMessage());
        }
        return holdings;
    }

    public static int getHolding(String playerName, String symbol) {
        String lowerSymbol = symbol.toLowerCase(); // 增加大小写转换
        String sql = "SELECT quantity FROM holdings WHERE player_name = ? AND LOWER(stock_symbol) = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, playerName);
            pstmt.setString(2, lowerSymbol); // 使用小写版本
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("quantity");
            }
        } catch (SQLException e) {
            Logger.error("获取持仓失败: " + e.getMessage());
        }
        return 0;
    }

    public static boolean isConnectionValid() {
        try {
            return connection != null && !connection.isClosed();
        } catch (SQLException e) {
            Logger.error("数据库连接状态检查失败: " + e.getMessage());
            return false;
        }
    }

    public static DatabaseType getDbType() {
        return dbType;
    }

    public static long getPurchaseTime(String playerName, String symbol) {
        String lowerSymbol = symbol.toLowerCase();
        String sql = "SELECT purchase_time FROM holdings WHERE player_name = ? AND LOWER(stock_symbol) = ?";

        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, playerName);
            pstmt.setString(2, lowerSymbol);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong("purchase_time");
            }
        } catch (SQLException e) {
            Logger.error("获取购买时间失败: " + e.getMessage());
        }
        return 0;
    }
}
